#!/usr/bin/python3
# -*- coding: utf-8 -*-
#======================================#
# @Author   : duanqizhong
# @time     : 2020-12-26 12:17
# @file     : client
# @Software : PyCharm
#======================================#
import pymysql
from config import DATABASE_STOCK

class Mysql:
    def __init__(self, conn=DATABASE_STOCK):
        self.conn = conn
        self.db = self.connect("stock")

    def connect(self, database=DATABASE_STOCK["database"]):
        """连接数据库"""
        self.db = pymysql.connect(host=self.conn["host"],
                                  port=self.conn["port"],
                                  user=self.conn["username"],
                                  passwd=self.conn["password"],
                                  database=database,
                                  use_unicode=True,
                                  charset='utf8')
        return self.db

    def commit(self):
        if self.db is not None:
            self.db.commit()

    def close(self):
        if self.db is not None:
            self.db.close()
            self.db = None

    def execute(self, sql):
        cursor = self.db.cursor()
        try:
            cursor.execute(sql)
            cursor.close()
        except Exception as e:
            self.db.rollback()
            print(e)
            return False
        return True

    def fetchone(self, sql):
        cursor = self.db.cursor()
        cursor.execute(sql)
        columns = [col[0] for col in cursor.description]
        record = cursor.fetchone()
        cursor.close()
        if record:
            return dict(zip(columns, record))
        return None

    def fetchall(self, sql):
        cursor = self.db.cursor()
        cursor.execute(sql)
        columns = [col[0] for col in cursor.description]
        all_result = [dict(zip(columns, row)) for row in cursor.fetchall()]
        cursor.close()
        return all_result
